S09-02 MySQL-基础SQL语句
[TOC]
DDL 基础
DDL（Data Definition Language，数据定义语言） 用于定义、管理与修改数据库结构（如数据库、数据表、索引、视图等）。在 MySQL 中，DDL 语句通常具有自动提交（Auto-Commit）特性，执行后不可通过事务进行回滚。
核心关键字包括：CREATE、ALTER、DROP、TRUNCATE 与 RENAME。
DDL 概述
DDL 主要覆盖以下几类数据库对象的管理：
- 数据库级别：创建、修改字符集及删除数据库。
- 数据表级别：定义字段数据类型、修改列结构、设置约束（主键、唯一键、外键）及清理表数据。
- 索引级别：针对单列或联合列创建索引以提高查询性能。
- 视图与触发器：定义虚拟表架构与自动化业务规则。
数据库操作
MySQL 中的 DATABASE（数据库）与 SCHEMA（模式）为同义词。数据库级别的 DDL 语句主要用于创建、查看、修改、删除数据库，以及控制其默认的字符集、排序规则和加密状态。
数据库级别的 DDL 操作直接决定了存储在服务层的数据集边界：
- 逻辑层面：数据库是表、视图、存储过程等对象的容器。
- 物理层面：在存储引擎层，每个数据库对应数据目录（
datadir）下的一个独立文件夹。
创建数据库
使用 CREATE DATABASE 或 CREATE SCHEMA 创建新的数据库实例。
语法结构：
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[CHARACTER SET [=] charset_name]
[COLLATE [=] collation_name]
[ENCRYPTION [=] {'Y' | 'N'}];参数说明：
IF NOT EXISTS：避免因数据库已存在而抛出中断错误（仅产生 Warning 警示）。CHARACTER SET：指定数据库默认字符集，建议使用utf8mb4（支持完整的 UTF-8 编码及 Emoji/生僻字）。COLLATE：指定排序规则（如utf8mb4_0900_ai_ci表示不区分大小写，utf8mb4_bin表示按二进制区分大小写）。ENCRYPTION：设置透明数据加密（TDE，MySQL 8.0.16 及以上支持）。
-- 创建具备特定字符集与排序规则的标准数据库
CREATE DATABASE IF NOT EXISTS order_system
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_0900_ai_ci;查看数据库
在对数据库进行维护前，可通过元查询指令检查已有状态。
查看所有数据库
sqlSHOW DATABASES;查看数据库定义语句
用于复核已建库的属性与配置：
sqlSHOW CREATE DATABASE order_system;
修改数据库
使用 ALTER DATABASE 变更已存在数据库的默认属性。
语法结构：
ALTER {DATABASE | SCHEMA} [db_name]
[CHARACTER SET [=] charset_name]
[COLLATE [=] collation_name]
[READ ONLY [=] {DEFAULT | 0 | 1}];核心操作场景：
修改默认字符集与排序规则：
sqlALTER DATABASE order_system DEFAULT CHARACTER SET = utf8mb4 DEFAULT COLLATE = utf8mb4_bin;注意：修改数据库默认字符集仅对后续新建的数据表生效，不会自动重构已有表的字段字符集。
设置只读状态（MySQL 8.0.22+）：
sql-- 锁定数据库，禁止写入与修改 ALTER DATABASE order_system READ ONLY = 1; -- 解除只读，恢复可写状态 ALTER DATABASE order_system READ ONLY = 0;
删除数据库
使用 DROP DATABASE 物理删除数据库及其内部所有对象（表、视图、触发器等）。
语法结构：
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name;操作说明：
彻底物理清理：该语句会立刻清理该数据库下的所有磁盘存储文件。
防错执行示例：
sqlDROP DATABASE IF EXISTS test_stage_db;
备份恢复数据库
MySQL 在早期版本（5.1.23）后彻底废弃了 RENAME DATABASE 指令，因为直接更改文件目录名极易破坏事务引擎（InnoDB）元数据的一致性。
如需对数据库进行重命名，需通过以下标准化步骤完成：
方式一：导出导入
导出旧库数据：使用
mysqldump工具备份全量数据。新建目标库：使用
CREATE DATABASE新建符合要求的新数据库。导入数据文件：将备份文件导入新数据库。
清理旧库：确认业务切换正常后，执行
DROP DATABASE删除原库。bash# 1. 备份旧库数据 mysqldump -u root -p --databases old_db1 old_db2 > old_db_backup.sql # 恢复方式一：新建新库 → 导入数据到新库 # 2. 新建新库 mysql -u root -p -e "CREATE DATABASE new_db CHARACTER SET utf8mb4;" # 3. 导入数据到新库 mysql -u root -p new_db < old_db_backup.sql # 恢复方式二：直接执行 source 命令（需要进入到 mysql 再操作） source old_db_backup.sql # 4. 删除旧库 mysql -u root -p -e "DROP DATABASE old_db;"
方式二：迁移数据表
创建新数据库：新建目标名称的数据库。
批量移动表路径：通过跨库执行
RENAME TABLE将所有表快速转存至新库。移除旧库：转移存储过程、视图与函数后删除空库。
sql-- 将表从旧库转移到新库（秒级完成，不涉及物理拷贝） RENAME TABLE old_db.users TO new_db.users, old_db.orders TO new_db.orders;
方式三：导出导入数据表
有的时候我们没有必要备份整个数据库，此时我们可以只备份其中的某些数据表。
# 1. 备份旧表数据
mysqldump -u root -p old_db old_table1 old_table2 > old_table_backup.sql
# 2. 恢复备份的表数据（与备份数据库语法一致，需要进入到 mysql 再操作）
source old_table_backup.sql底层运行机制
数据库级别 DDL 的执行与 MySQL 系统的整体架构紧密相关：
文件系统交互：执行
CREATE DATABASE时，存储引擎会在系统的datadir路径下建立与数据库名同名的文件夹。元数据控制：在 MySQL 8.0 之前，数据库配置记录在文件夹中的
db.opt文件内；MySQL 8.0 之后，配置被统一集成入 InnoDB 数据字典（System Data Dictionary）。原子性保障（Atomic DDL）：MySQL 8.0 提供了原子 DDL 机制，数据库级别的创建与删除均被写入日志，确保中途异常崩溃时能正确回滚，防止产生不完整残留。
数据表操作
数据表（Table） 是 MySQL 中存储数据的基本逻辑单元。数据表级别的 DDL 语句主要用于定义、修改和销毁数据表的结构、列属性、约束定义以及存储引擎配置。
在 MySQL（尤其是 InnoDB 存储引擎）中，表结构与底层物理文件紧密挂钩：
- 逻辑层面：包含列名、数据类型、主键、索引、默认值及外键等约束。
- 物理层面：在 MySQL 8.0 中，数据表结构与元数据统一存储在
mysql.ibd数据字典中；表数据与索引存储在独立的.ibd表空间文件中。
创建数据表
使用 CREATE TABLE 定义全新的表结构。
语法定义：
CREATE TABLE [IF NOT EXISTS] table_name (
column_1 data_type [column_constraint],
column_2 data_type [column_constraint],
...
[table_constraint]
) ENGINE=engine_name
DEFAULT CHARSET=charset_name
COLLATE=collation_name
COMMENT='表注释';完整示例：
CREATE TABLE IF NOT EXISTS orders (
order_id BIGINT UNSIGNED AUTO_INCREMENT COMMENT '订单ID',
user_id BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
amount DECIMAL(10, 2) NOT NULL DEFAULT 0.00 COMMENT '订单金额',
status TINYINT NOT NULL DEFAULT 0 COMMENT '状态: 0待支付 1已支付 2已取消',
remark VARCHAR(255) DEFAULT NULL COMMENT '备注',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (order_id),
KEY idx_user_id (user_id),
KEY idx_created_at (created_at),
CONSTRAINT chk_amount CHECK (amount >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='订单主表';复制建表：
仅复制表结构（包含索引与约束）：
sqlCREATE TABLE orders_bak LIKE orders;基于查询结果创建表（不复制索引与约束）：
sqlCREATE TABLE orders_2026 AS SELECT * FROM orders WHERE created_at >= '2026-01-01';
修改数据表
使用 ALTER TABLE 改变已有表的结构定义。
列操作
-- 1. 添加新列 (支持通过 FIRST 或 AFTER 指定相对位置)
ALTER TABLE orders ADD COLUMN pay_type TINYINT DEFAULT 1 COMMENT '支付方式' AFTER amount;
-- 2. 删除列
ALTER TABLE orders DROP COLUMN remark;
-- 3. 修改列属性 (保持列名不变，修改类型或约束)
ALTER TABLE orders MODIFY COLUMN pay_type SMALLINT DEFAULT 1 COMMENT '扩展支付方式';
-- 4. 重命名列 (同时可修改类型与约束)
ALTER TABLE orders CHANGE COLUMN pay_type payment_method TINYINT DEFAULT 1 COMMENT '支付渠道';约束与索引操作
-- 添加主键
ALTER TABLE orders ADD PRIMARY KEY (order_id);
-- 删除主键 (如果主键为 AUTO_INCREMENT，需先用 MODIFY 移除自增属性)
ALTER TABLE orders DROP PRIMARY KEY;
-- 添加外键约束
ALTER TABLE orders
ADD CONSTRAINT fk_orders_user
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;
-- 删除外键约束
ALTER TABLE orders DROP FOREIGN KEY fk_orders_user;表选项修改
-- 修改存储引擎
ALTER TABLE orders ENGINE = InnoDB;
-- 修改字符集与排序规则 (会转换表中现存字段的数据字符集)
ALTER TABLE orders CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
-- 重置自增计数器起始值
ALTER TABLE orders AUTO_INCREMENT = 10000;清空与删除
清理表数据或直接移除整个表结构。
语法结构：
-- 1. 重命名表
RENAME TABLE orders TO user_orders;
-- 2. 清空表数据 (快速重置表)
TRUNCATE TABLE user_orders;
-- 3. 删除表
DROP TABLE IF EXISTS user_orders;数据清理机制对比：
DROP TABLE ---> 销毁表结构 + 清空物理文件
TRUNCATE TABLE ---> 物理截断文件 (重建空表 + 重置自增主键)
DELETE FROM ---> 逐行扫描删除 (产生 Undo/Redo 日志)资源释放：
TRUNCATE会直接清空数据页并释放物理存储空间，而DELETE即使删除了所有行，占用的磁盘空间默认不会立刻释放。事务支持：
TRUNCATE是 DDL 语句，不支持事务回滚；DELETE是 DML 语句，可在事务中进行ROLLBACK。
执行机制解析
MySQL 在执行 ALTER TABLE 时，根据版本与语句类型会使用不同的执行策略（Online DDL）：
不同变更算法对读写锁及性能的影响区别如下：
| 算法机制 | 允许读 (SELECT) | 允许写 (DML) | 运行原理 | 典型适用场景 |
|---|---|---|---|---|
| COPY | 是 | 否 (锁表) | 创建新临时表，阻塞写操作，将旧表数据全量复制到新表。 | 修改字段数据类型（如 INT 改 VARCHAR） |
| INPLACE | 是 | 是 | 在原表空间内部操作（重建表或仅改元数据），记录变更日志并在最后阶段同步。 | 添加/修改索引、添加新列 |
| INSTANT | 是 | 是 | 仅修改数据字典元数据，瞬间完成（MySQL 8.0+ 支持）。 | 在表末尾添加新列、修改列默认值 |
生产变更步骤
在生产环境下针对大型数据表（千万级以上）执行 DDL 操作时，为避免引发长时间锁表，必须遵守标准运维流程：
检测环境与锁状态：排查当前表是否有长事务或高并发未提交连接，防止 DDL 被 MDL（元数据锁）阻塞。
指定 Online 参数：显式声明
ALGORITHM=INPLACE, LOCK=NONE，若不支持并发写入则立即中断执行。评估磁盘空间：若涉及表重建（Rebuild Table），需确保磁盘剩余空间大于目标表大小的 2 倍。
使用第三方无锁工具：对于非常庞大的核心表，优先使用
gh-ost或pt-online-schema-change等工具，通过影子表加 Binlog 增量追平的方式平滑切表。
DDL 高级
索引操作
MySQL 中的索引（Index）是存储引擎用于快速检索数据行的有序数据结构。索引级别的 DDL 语句主要用于创建、修改、重命名、隐藏以及删除索引。
在 InnoDB 存储引擎中，索引基于 B+ 树数据结构实现，索引的定义与变更直接影响数据库的磁盘 I/O 开销与查询性能。
概念解析
MySQL 中的索引按照逻辑和物理特性划分为不同的类型：
- 主键索引（Primary Key）：数据表的核心索引，列值必须唯一且非空。InnoDB 以主键构建聚簇索引（Clustered Index），表数据物理存放在主键的 B+ 树叶子节点上。
- 唯一索引（Unique Index）：确保字段列的值唯一，允许存在
NULL值。 - 普通索引（Normal / Secondary Index）：最基础的二级索引，叶子节点存储主键值（非全量行数据），查询时需通过主键二次“回表”获取完整的行记录。
- 联合索引（Composite Index）：由多个字段组合建立的索引，遵循“最左前缀匹配原则”。
- 全文索引（Fulltext Index）：专门针对长文本提取关键词构建倒排索引（Inverted Index），用于文本搜索。
创建索引
在 MySQL 中，可以通过 CREATE INDEX 语句或 ALTER TABLE 语句创建索引。
语法结构与示例
-- 方式一：使用 CREATE INDEX 语句
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...)
[ALGORITHM = {DEFAULT | INPLACE | COPY}]
[LOCK = {DEFAULT | NONE | SHARED | EXCLUSIVE}];
-- 方式二：使用 ALTER TABLE 语句
ALTER TABLE table_name
ADD INDEX index_name (column_1, column_2);核心创建场景
创建联合索引：
sqlCREATE INDEX idx_user_status ON users (status, created_at);创建前缀索引（优化长字符串存储）：
只提取字符串的前 个字符构建索引，降低 B+ 树节点的大小。
sql-- 对 email 字段的前 20 个字符建立索引 CREATE INDEX idx_email_prefix ON users (email(20));创建降序索引（MySQL 8.0+）：
解决多字段混合排序（如
ORDER BY a ASC, b DESC）时无法高效利用索引的问题。sqlCREATE INDEX idx_status_time ON orders (status ASC, created_at DESC);
查看与修改
针对已存在的索引，可以进行重命名、隐藏以及状态维护。
查看表中索引
SHOW INDEX FROM users;重命名索引
ALTER TABLE users RENAME INDEX idx_status TO idx_user_status;控制索引可见性
隐藏索引（Invisible Index）允许将索引设置为对优化器不可见，但后台仍会同步维护索引数据。
-- 将索引设置为隐藏（优化器生成执行计划时忽略该索引）
ALTER TABLE users ALTER INDEX idx_user_status INVISIBLE;
-- 恢复索引可见性
ALTER TABLE users ALTER INDEX idx_user_status VISIBLE;删除索引
删除不再需要的索引能够降低数据写操作（INSERT/UPDATE/DELETE）时的维护成本并节省磁盘空间。
语法结构与示例
-- 方式一：直接使用 DROP INDEX
DROP INDEX idx_user_status ON users;
-- 方式二：使用 ALTER TABLE DROP INDEX
ALTER TABLE users DROP INDEX idx_user_status;
-- 方式三：删除主键索引
ALTER TABLE users DROP PRIMARY KEY;注意：如果主键列包含
AUTO_INCREMENT属性，直接删除主键会报错，必须先通过ALTER TABLE ... MODIFY移除自增属性后方可删除。
执行机制
创建与删除索引时的底层资源开销因索引类型而异：
| 变更类型 | 算法 (Algorithm) | 锁级别 (Lock) | 运行机制与影响 |
|---|---|---|---|
| 新增二级索引 | INPLACE | NONE | 允许并发 DML 写入，在原表空间追加索引 B+ 树节点。 |
| 删除二级索引 | INSTANT / INPLACE | NONE | 仅修改数据字典元数据并释放索引页，瞬间完成。 |
| 新增/修改主键 | INPLACE | NONE / SHARED | 需要重构整张表（Rebuild Table）以及对应的所有二级索引，开销极大。 |
线上变更步骤
在千万级以上的数据量规模下添加或删除索引，应遵循以下操作流程以保障服务稳定性：
分析索引必要性与区分度：通过
SELECT COUNT(DISTINCT col) / COUNT(*)评估列的选择性，选择性高的字段才适合建索引。测试隐藏索引（降级验证）：若打算删除线上索引，先将其设置为
INVISIBLE并观察数小时或数天。如果系统没有出现慢查询，再进行物理删除。显式指定无锁参数：执行
ALTER TABLE时显示带上ALGORITHM=INPLACE, LOCK=NONE选项，防止误锁表。监控系统负载与复制延迟：大规模构建索引需要大量磁盘 I/O 和 CPU 排序资源，需密切关注主从同步延迟（Slave Lag）及 IOPS 指标。
视图操作
视图（View）是 MySQL 中的一种虚拟表，其内容由查询语句（SELECT）动态定义。视图本身不包含物理数据，行和列数据均来自定义视图时引用的真实数据表（基本表）。
针对视图的 DDL 语句主要用于创建、重定义、修改配置以及销毁视图。
概念解析
视图在数据库体系结构中起到了屏蔽底层复杂逻辑、提供统一数据接口的作用：
- 逻辑层分离：视图属于数据库“三级模式”中的外模式（External Schema），通过将复杂的多表关联查询封装为视图，可以向上层应用隐藏底层的表结构设计。
- 数据安全控制：针对敏感数据表，可以通过视图只暴露特定列或过滤后的行，从而实现列级和行级的数据访问权限控制。
创建视图
使用 CREATE VIEW 语句定义新的视图架构。
语法定义
CREATE [OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = user]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION];关键属性解析
OR REPLACE：如果指定的视图名称已存在，则自动替换原视图定义。ALGORITHM（处理算法）：MERGE：合并算法。将查询视图的 SQL 与视图定义的 SELECT 语句合并后再执行，性能较好。TEMPTABLE：临时表算法。优先将视图结果集写入内部临时表，然后再对其进行查询。临时表算法创建的视图不可更新。UNDEFINED：默认值。由 MySQL 优化器自动选择MERGE或TEMPTABLE。
SQL SECURITY（安全上下文）：DEFINER：默认值。以创建者（DEFINER）的权限来执行该视图。INVOKER：以调用者（当前执行查询的用户）的权限来执行该视图。
WITH CHECK OPTION（检查选项）：当通过视图进行 DML 操作（
INSERT或UPDATE）时，强制校验插入的数据必须满足视图定义中的WHERE约束。CASCADED：默认值。递归检查当前视图及其依赖的所有底层视图的条件。LOCAL：仅检查当前视图的定义，只在底层视图显式声明了检查选项时才进行递归检查。
创建示例
-- 创建一个仅展示已支付订单的视图，并开启级联检查选项
CREATE OR REPLACE VIEW v_paid_orders AS
SELECT
order_id,
user_id,
amount,
created_at
FROM orders
WHERE status = 1
WITH CASCADED CHECK OPTION;查看与修改
通过元数据查询以及专门的修改指令对视图状态进行维护。
查看视图信息
-- 1. 查看当前数据库下的所有视图及数据表
SHOW FULL TABLES WHERE Table_type = 'VIEW';
-- 2. 查看具体视图的创建 DDL 定义
SHOW CREATE VIEW v_paid_orders;
-- 3. 查看视图的列字段结构信息
DESCRIBE v_paid_orders;修改视图结构
修改视图可以通过 CREATE OR REPLACE VIEW 或 ALTER VIEW 语句完成：
ALTER ALGORITHM = MERGE VIEW v_paid_orders AS
SELECT
order_id,
user_id,
amount,
status,
created_at
FROM orders
WHERE status = 1;删除视图
使用 DROP VIEW 语句物理清理视图定义。
语法结构
DROP VIEW [IF EXISTS] view_name [, view_name2 ...]
[RESTRICT | CASCADE];示例与注意项
-- 一次性删除多个视图
DROP VIEW IF EXISTS v_paid_orders, v_user_summary;删除视图仅仅移除数据字典中的视图元数据定义，完全不会删除或破坏底层基本表中的数据。
可更新限制
通过视图不仅能进行查询，在特定条件下还能对视图执行 DML（INSERT/UPDATE/DELETE）操作，写操作会直接作用于底层基本表。但当视图定义包含以下情况时，视图将变为不可更新：
| 不可更新特征 | 示例说明 / 涉及关键字 |
|---|---|
| 聚合函数 | 包含 SUM(), MIN(), MAX(), COUNT() 等 |
| 排他过滤与去重 | 使用了 DISTINCT 关键字 |
| 分组与筛选 | 使用了 GROUP BY 或 HAVING 子句 |
| 集合操作 | 使用了 UNION 或 UNION ALL |
| 特定算法视图 | ALGORITHM 显式指定为 TEMPTABLE |
| 派生列 | 包含数学表达式、拼接函数等不可还原字段（如 amount * 0.9 AS discount_amount） |
| 子查询或连接 | 含有 FROM 子句中的不可更新子查询或复杂的非等值连接 |
运维执行步骤
在生产环境下使用和维护视图时，应遵循以下规范步骤：
评估权限与安全性：针对提供给第三方或跨团队调用的视图，尽量指定
SQL SECURITY INVOKER，避免提升调用者未授权数据表的访问权限。测试评估执行计划：在复杂视图外层叠加筛选条件时，通过
EXPLAIN查看优化器是否成功执行了MERGE算法。若降级为TEMPTABLE，大表扫描时可能产生高额磁盘 I/O。保持基表变更同步：底层基本表如果删除了字段，依赖该字段的视图不会自动报错，但在查询视图时会报
View ... references invalid table(s) or column(s)错误。表结构变更后需重新校对视图定义。
DML
概述
DML（Data Manipulation Language，数据操作语言）是 SQL 中用于对数据库表中的数据进行增、删、改等操作的核心指令集。在 MySQL 中，核心 DML 包括 INSERT、UPDATE、DELETE 与 MySQL 专有的 REPLACE 语句。
事务与锁控制：
在 InnoDB 存储引擎中，所有 DML 语句都在事务边界内执行，直接涉及行锁（Row Lock）、间隙锁（Gap Lock）与临键锁（Next-Key Lock）。
显式事务与锁定读取
通过显式事务包裹 DML 操作，并使用悲观排他锁（FOR UPDATE）保障并发一致性：
-- 开启显式事务并使用排他锁锁定目标行
START TRANSACTION;
SELECT balance
FROM accounts
WHERE account_id = 1001
FOR UPDATE;
-- 扣减账户余额并在完成后提交事务
UPDATE accounts
SET balance = balance - 200
WHERE account_id = 1001;
COMMIT;DML 性能优化规范
- 覆盖索引避免锁升级：确保
UPDATE和DELETE的WHERE条件命中索引，否则可能引发锁表。 - 避免长事务：大批量 DML 切分成小批次提交，降低 Undo Log 膨胀风险与主从复制延迟。
- 控制主键有序插入：批量
INSERT时按主键升序排列，能显著减少 InnoDB B+ 树的页分裂（Page Split）。
INSERT
INSERT 是 MySQL 中用于向数据库表中写入一条或多条记录的核心 DML 语句。在 InnoDB 存储引擎中，数据的插入会直接影响聚簇索引结构、自增锁机制以及 Undo/Redo 日志的生成。
VALUES 插入
通过 VALUES 子句插入数据是 MySQL 最标准的插入方式，支持单行写入与高吞吐的批量多行写入。
特性与语法规范
列与值严格对应：如果省略列清单，
VALUES列表中的值必须与表定义中的所有列顺序完全一致。批量插入开销极低：多行插入合并为一条 SQL 发送，大幅降低网络往返延迟（RTT）、减少事务开销并共享日志刷盘。
sql-- 基础单行与批量多行插入语法 INSERT INTO users (username, email, status) VALUES ('alice', 'alice@example.com', 1), ('bob', 'bob@example.com', 0);
SET 插入
INSERT ... SET 是 MySQL 提供的扩展语法，采用类似 UPDATE 语句的键值对形式指定列与对应值。
特性与限制
仅支持单行写入：该语法无法实现多行数据的批量写入。
不支持从子查询注入：无法配合
SELECT结果集进行写入。可读性高：在少量特定字段写入时，字段与数值绑定明确，减少参数对齐错误。
sql-- 使用 SET 子句显式赋值插入单行记录 INSERT INTO users SET username = 'charlie', email = 'charlie@example.com', status = 1;
SELECT 插入
INSERT INTO ... SELECT 将源表的查询结果集直接写入目标表中，常用于报表聚合、历史数据归档与数据清洗。
数据同步机制
执行
SELECT子查询检索源表数据。校验查询列与目标表列的数据类型兼容性。
批量流式写入目标表，单条语句作为一个原子事务。
sql-- 从订单表中筛选已完成订单归档至历史表 INSERT INTO order_archive (order_id, user_id, amount) SELECT id, user_id, total_price FROM orders WHERE order_status = 'COMPLETED';
注意：在
REPEATABLE READ隔离级别下，源表被查询扫描到的数据范围可能会被加上共享锁（S 锁），避免在并发写入时产生数据不一致。
IGNORE 插入
在 INSERT 关键字后增加 IGNORE 修饰符，可使语句在触发主键（PRIMARY KEY）或唯一索引（UNIQUE KEY）冲突时静默忽略，不中断整批任务。
冲突处理与返回值
错误降级为警告：重复键错误被降级为 Warning，不抛出异常。
受影响行数：冲突行被跳过，
affected_rows计为 0，仅成功写入的行计为 1。sql-- 遇到唯一索引或主键冲突时静默跳过插入 INSERT IGNORE INTO user_tokens (user_id, token_hash) VALUES (1001, 'hash_abc123');
冲突更新
ON DUPLICATE KEY UPDATE 用于实现 "存在即更新，不存在即插入"（Upsert）的原子操作。
执行机制与影响行数
新行插入：未发生唯一键冲突，直接插入，影响行数返回
1。已有行更新：命中唯一键冲突，就地更新指定列，影响行数返回
2。值无变更：命中唯一键冲突但更新后的值与原值完全一致，影响行数返回
0。sql-- 存在唯一键冲突时更新指定字段 INSERT INTO page_views (page_id, view_count, updated_at) VALUES (205, 1, NOW()) ON DUPLICATE KEY UPDATE view_count = view_count + 1, updated_at = VALUES(updated_at);
MySQL 8.0+ 别名语法
从 MySQL 8.0.20 开始，VALUES(col_name) 函数已被弃用，官方推荐使用行别名（Row Alias）引用待插入的新行：
-- MySQL 8.0.20+ 推荐使用别名引用新插入行的数据
INSERT INTO page_views (page_id, view_count, updated_at) AS new_row
VALUES (205, 1, NOW())
ON DUPLICATE KEY UPDATE
view_count = page_views.view_count + 1,
updated_at = new_row.updated_at;写入机制与调优
在 InnoDB 存储引擎中，INSERT 的执行效率直接受聚簇索引物理结构与锁模式控制。
核心优化规范
- 主键顺序写入：使用自增主键或单调递增雪花算法。无序写入（如 UUID）会导致 B+ 树叶子节点频繁发生页分裂（Page Split）和页合并，引发随机 I/O 与碎片率剧增。
- 控制批量大小：单次批量插入控制在 500 到 2000 行之间，避免超出
max_allowed_packet或导致单个事务锁持有时间过长。 - 自增锁模式优化：配置
innodb_autoinc_lock_mode = 2（交错锁模式，MySQL 8.0 默认），彻底消除表级自增锁竞争，显著提升并发并发批量INSERT吞吐。
UPDATE
UPDATE 是 MySQL 中用于修改表中已存在数据的核心 DML 语句。在 InnoDB 存储引擎中，UPDATE 涉及行级排他锁加锁、Undo Log 版本链生成、Redo Log 预写（WAL）以及两阶段提交等底层机制。
单表更新
单表更新用于对单张数据表中满足特定条件的记录进行修改。
基础更新与算术表达式
更新操作支持直接赋值或基于已有值进行算术运算（如数值累加、字符串拼接）：
-- 基础单表更新并使用表达式修改字段值
UPDATE user_accounts
SET balance = balance + 500.00,
updated_at = NOW()
WHERE user_id = 1002;排序与限量更新
结合 ORDER BY 与 LIMIT 子句可以精确控制更新顺序与更新的记录上限，常用于定时任务分批消费或限流更新：
-- 按创建时间升序修改前100条待处理任务状态
UPDATE task_queue
SET status = 'PROCESSING',
retry_count = retry_count + 1
WHERE status = 'PENDING'
ORDER BY created_at ASC
LIMIT 100;多表更新
多表更新允许基于多个表之间的关联关系（JOIN）同时修改一张或多张表中的数据。
内连接与外连接更新
在关联更新时，可以使用标准 JOIN 语法指定联表匹配条件：
-- 关联更新：根据会员等级批量调整订单折扣与结算金额
UPDATE orders o
JOIN customers c
ON o.customer_id = c.id
SET o.discount_rate = c.vip_discount,
o.final_amount = o.total_amount * (1 - c.vip_discount)
WHERE c.status = 'ACTIVE'
AND o.payment_status = 'UNPAID';语法限制：在 MySQL 多表更新语法中，不支持使用
ORDER BY和LIMIT子句。
分支更新
当需要对同一批数据根据不同条件赋予不同值时，使用 CASE ... WHEN 表达式可以在单条 SQL 语句中完成批量差异化更新，避免频繁建立网络连接。
批量差异化更新
-- 基于 CASE 表达式对多个用户批量设置不同的等级与积分
UPDATE users
SET level = CASE id
WHEN 1 THEN 'VIP1'
WHEN 2 THEN 'VIP2'
WHEN 3 THEN 'VIP3'
ELSE level
END,
points = CASE id
WHEN 1 THEN points + 100
WHEN 2 THEN points + 200
WHEN 3 THEN points + 300
ELSE points
END
WHERE id IN (1, 2, 3);子查询更新
MySQL 不允许在 UPDATE 语句的子查询中直接从被更新的同一张目标表中读取数据（触发 Error 1093）。为了规避该限制，需将子查询包装为派生表（Derived Table）并通过 JOIN 关联更新。
派生表关联更新
-- 规避 Error 1093：利用派生表计算聚合值并回填到目标表
UPDATE products p
JOIN (
SELECT product_id, AVG(rating) AS avg_score
FROM product_reviews
WHERE is_valid = 1
GROUP BY product_id
) AS review_summary
ON p.id = review_summary.product_id
SET p.rating_score = review_summary.avg_score,
p.review_count = p.review_count + 1;IGNORE 更新
在 UPDATE 关键字后添加 IGNORE 修饰符，可使语句在执行遇到特定非致命错误时静默降级为警告，保证整批执行不被中断。
降级与容错场景
唯一键冲突：更新产生的主键或唯一索引重复记录时，冲突行将被跳过，不更新该行。
数据类型截断：字符串超长或浮点精度溢出时自动截断为最大有效长度，而非抛出异常。
空值约束违规：向
NOT NULL列写入NULL时自动赋予字段默认零值。sql-- 遇到唯一索引冲突或数据截断时跳过异常并继续执行 UPDATE IGNORE user_profiles SET unique_code = 'CODE_8899' WHERE user_id = 205;
底层执行机制
在 InnoDB 存储引擎中，UPDATE 语句的执行涉及内存缓冲池、日志子系统与锁管理器的协同工作。
核心执行流程
语法解析与计划生成：Server 层分析器验证语法并由优化器选定最优执行路径与索引。
数据检索与加锁：存储引擎根据
WHERE条件定位行记录，对目标行施加排他锁（X-Lock，行锁或 Next-Key Lock）。记录 Undo Log：将数据修改前的旧版本镜像写入 Undo Page，供事务回滚和并发事务的 MVCC 快照读使用。
内存页修改：在 Buffer Pool 中将目标 Data Page 修改为新值，标记为脏页（Dirty Page）。
记录 Redo Log：将物理数据页变更写入 Redo Log Buffer，保证事务的持久性（WAL 机制）。
两阶段提交（2PC）：
- 存储引擎将 Redo Log 刷盘并置为
prepare状态。 - Server 层生成逻辑日志写入 Binlog 缓存并持久化到磁盘。
- 存储引擎将 Redo Log 置为
commit状态，完成事务提交。
- 存储引擎将 Redo Log 刷盘并置为
行内原地更新与删除标记重建
- In-Place Update（就地更新）：如果未修改主键，且所有被修改字段的存储空间长度未超出原空间，InnoDB 直接在原记录位置原地覆盖修改。
- Delete-Mark + Insert：如果更新了主键，或者变长字段（如
VARCHAR）扩展导致当前数据槽位空间不足，InnoDB 会先对原记录打上删除标记（Delete-Mark），再在合适位置插入一条新记录。
优化与安全规范
生产环境最佳实践
- 强制命中索引更新：
WHERE条件必须命中索引（优先使用主键或唯一索引）。若走全表扫描，InnoDB 会升级为对整张表的所有记录及间隙加排他锁，阻塞其他所有并发写入。 - 开启安全更新模式：在会话或全局开启
sql_safe_updates = 1，强制拒绝未带索引条件或未加LIMIT的无条件全表UPDATE。 - 大事务分批处理：百万级数据修改应按主键范围拆分为单次几千条的小批次循环执行，防止 Undo Log 空间膨胀、主从复制延迟及锁超时（Lock Wait Timeout）。
REPLACE
REPLACE 是 MySQL 对标准 SQL 的专有扩展 DML 语句。它的核心逻辑是先尝试插入，若发生主键或唯一索引冲突，则先删除冲突的旧行，再插入新行。
VALUES 替换
通过 VALUES 子句进行数据覆盖替换，语法格式与 INSERT INTO ... VALUES 保持一致，支持单行与批量多行操作。
语法特性
全行覆盖：未显式指定的字段将自动回退为列的默认值或
NULL，不会保留历史旧值。批量吞吐优化：单条语句传递多组值可减少网络 I/O 与日志刷盘频次。
sql-- 单行与批量主键或唯一键冲突覆盖写入 REPLACE INTO user_sessions (session_id, user_id, ip_address, expires_at) VALUES ('sess_1001', 101, '192.168.1.10', '2026-08-18 12:00:00'), ('sess_1002', 102, '192.168.1.11', '2026-08-18 12:30:00');
SET 替换
REPLACE INTO ... SET 采用类似 UPDATE 的键值对赋值风格，适用于字段较少且需要直观赋值的单行写入场景。
语法特性
仅支持单行操作：无法直接在单条语句中追加多行数据。
语义等同覆盖：虽然语法外观类似
UPDATE，但底层仍遵循“删除旧行并插入新行”的机制，未声明的列依然会被重置为默认值。sql-- 使用 SET 子句以键值对形式覆盖写入单条记录 REPLACE INTO app_configs SET config_key = 'MAX_CONNECTIONS', config_value = '500', updated_at = NOW();
SELECT 替换
REPLACE INTO ... SELECT 将查询结果集流式写入目标表中。如果源数据中的键与目标表已有记录冲突，则会直接替换目标表中的旧数据。
语法特性
多用于数据同步与重算：在离线汇总、日结快照、ETL 管道中，常用于幂等性重跑与全量覆盖写入。
类型与列数对齐：
SELECT投影出的列数量与类型必须与REPLACE INTO指定的目标列严格兼容。sql-- 将源表聚合结果以覆盖方式同步至每日排行榜表 REPLACE INTO daily_user_ranks (user_id, rank_score, rank_date) SELECT user_id, score, CURRENT_DATE FROM game_scores WHERE game_date = CURRENT_DATE;
执行机制
REPLACE 语句的执行依赖于表上的主键（PRIMARY KEY）或唯一索引（UNIQUE KEY）。
执行流程
存储引擎尝试向表中直接插入目标新行。
若未发生主键或唯一键冲突，直接完成写入，返回受影响行数
1。若检测到唯一约束冲突，定位所有产生冲突的已有记录并执行物理删除（DELETE）。
将新行完整插入数据表中，返回受影响行数
2（若单行数据同时与多条历史记录产生不同唯一键冲突并全部删除，受影响行数将大于 2）。
对比与选型
在 MySQL 中，REPLACE INTO 与 INSERT ... ON DUPLICATE KEY UPDATE 常用于处理重复键逻辑，但两者的底层行为与数据安全性差异显著。
| 维度 | REPLACE INTO | INSERT ... ON DUPLICATE KEY UPDATE |
|---|---|---|
| 底层实现 | 物理删除（DELETE）+ 新增插入（INSERT） | 原地更新（In-Place UPDATE） |
| 未指定列处理 | 强制重置为列默认值或 NULL | 完全保留原有的历史字段值 |
| 自增主键（AUTO_INCREMENT） | 若删除后未指定自增 ID，会生成新的自增 ID | 主键保持不变，不消耗新的自增 ID |
| 触发器激活 | 激活 DELETE 和 INSERT 触发器 | 激活 UPDATE 触发器 |
| 外键关联影响 | 容易触发级联删除（CASCADE）或外键报错（RESTRICT） | 仅更新字段，安全受外键保护 |
风险与规范
生产环境避坑点
- 历史数据静默丢失：如果业务意图仅是更新部分字段，切勿使用
REPLACE。任何未在 SQL 中明确声明赋值的列都会被覆盖为初始默认值。 - 外键级联灾难：如果当前表作为主表被其他子表通过外键关联，
REPLACE触发的底层DELETE会导致关联子表数据被CASCADE误删，或在ON DELETE RESTRICT下直接抛出外键约束错误。 - 自增 ID 膨胀与断层：当主键为自增列且冲突发生在非主键的唯一索引时，旧行被删除，新插入行将分配更大的自增主键，导致自增 ID 快速被消耗。
- Binlog 模式与复制放大：在
ROW模式的 Binlog 下，一次冲突REPLACE会记录一条DELETE事件与一条WRITE事件，增加主从同步开销。
DELETE
DELETE 是 MySQL 中用于从表中移除已有数据行的核心 DML 语句。在 InnoDB 存储引擎中，DELETE 并不会立即物理抹去磁盘数据，而是通过标记删除、Undo 日志记录、版本链维护以及后台异步 Purge 线程协作完成。
单表删除
单表删除用于从单个数据表中移除满足 WHERE 过滤条件的记录。
基础删除与排序限量
严防全表删除：若省略
WHERE子句，将逐行扫描并删除整张表的数据。分批与限流控制：结合
ORDER BY与LIMIT子句可以限制单次事务删除的数据量，降低行锁持有时间与主从同步延迟。sql-- 按条件与主键排序限流删除历史数据 DELETE FROM audit_logs WHERE created_at < '2025-01-01 00:00:00' ORDER BY id ASC LIMIT 1000;
多表删除
多表删除允许根据跨表关联条件（JOIN）从一个或多个目标表中同时删除数据。
语法结构与应用场景
删除单张关联表：在
DELETE与FROM之间仅声明需要被删除数据的表别名。同时删除多张表：在
DELETE与FROM之间声明多个表别名，实现多表同步物理清理。sql-- 跨表级联删除订单主表及关联明细数据 DELETE o, i FROM orders o JOIN order_items i ON o.id = i.order_id WHERE o.status = 'CANCELLED' AND o.created_at < '2025-01-01 00:00:00';
子查询删除
MySQL 不支持在 DELETE 的子查询中直接引用当前正在执行删除的同一张目标表（触发 Error 1093）。需要将查询逻辑包装为派生表（Derived Table）或使用 JOIN 关联删除。
派生表关联删除
-- 规避 Error 1093：利用派生表清理重复数据并保留最小 ID
DELETE t1
FROM user_emails t1
JOIN (
SELECT email, MIN(id) AS min_id
FROM user_emails
GROUP BY email
HAVING COUNT(*) > 1
) t2
ON t1.email = t2.email
AND t1.id > t2.min_id;底层执行机制
在 InnoDB 存储引擎中，DELETE 操作采用软删除结合异步垃圾回收的架构。
执行流程
加排他锁（X-Lock）：存储引擎定位待删除记录，根据事务隔离级别施加行级排他锁或临键锁（Next-Key Lock）。
生成 Undo Log：将当前记录的完整数据镜像写入 Undo Page，形成版本链供 MVCC 快照读和事务回滚使用。
标记删除（Delete-Mark）：将记录头部的
deleted_flag标志位设为 1，此时数据仍物理存在于 B+ 树数据页内。异步清理（Purge）：事务提交后，当确认没有任何活跃事务的 Read View 依赖该版本时，后台 Purge 线程执行真正的物理空间释放，将该槽位放入数据页的垃圾链表（Garbage List）供后续
INSERT复用。
对比与选型
在 MySQL 中，DELETE、TRUNCATE 与 DROP 均可用于移除数据，但其底层定位和性能特征各不相同。
| 维度 | DELETE (DML) | TRUNCATE (DDL) | DROP (DDL) |
|---|---|---|---|
| 操作粒度 | 逐行删除，支持 WHERE 条件 | 清空整张表数据 | 销毁整张表结构及数据 |
| 事务支持 | 完整事务支持，可回滚（ROLLBACK） | 触发隐式提交，不可回滚 | 触发隐式提交，不可回滚 |
| 空间回收 | 仅内部复用，不立即缩减 .ibd 磁盘文件 | 重新创建表空间，立即释放磁盘空间 | 立即释放表空间及文件 |
| 自增计数器 | 保留当前 AUTO_INCREMENT 计数值 | 重置 AUTO_INCREMENT 为初始值 | 表结构连同计数器一并销毁 |
| 触发器 | 逐行触发 DELETE 触发器 | 不激活任何触发器 | 不激活任何触发器 |
大表删除规范
对千万级以上的大表直接执行全表或大范围 DELETE 会导致主从复制高延迟、长事务死锁以及磁盘碎片膨胀。
生产环境最佳实践
- 主键分批循环删除：通过外部脚本按主键 ID 范围拆解为每次 1000 到 5000 条的小批次，并在批次间短暂
sleep，避免长时间占用锁和写满 Undo 表空间。 - 利用 pt-archiver 工具：在生产归档中优先使用 Percona 工具集中的
pt-archiver，以流式安全的方式批量抽取并删除历史数据。 - 重建表空间消除碎片：大批量
DELETE后，B+ 树页内会产生大量空洞碎片。可通过执行OPTIMIZE TABLE 表名或ALTER TABLE 表名 ENGINE=InnoDB重构表空间，物理收缩.ibd文件。 - 开启安全更新模式：在生产数据库中设置
SET sql_safe_updates = 1;，严格杜绝未携带索引字段或未加LIMIT的危险删除。
TRUNCATE
TRUNCATE 在日常业务中常被用于快速清空表数据，但在 MySQL 内部及 SQL 标准中，它在本质上属于 DDL（数据定义语言） 而非 DML。TRUNCATE 通过物理销毁并重建底层表空间来清空数据，执行时会触发隐式提交且不可回滚。
语法与用法
TRUNCATE 语法精炼，不支持 WHERE 过滤条件，直接作用于整张表或指定分区。
-- 快速清空指定数据表的全部记录并重置元数据
TRUNCATE TABLE user_logs;分区表截断
对于基于范围或列表划分的分区表，可以单独截断指定分区的数据，而不影响其他分区：
-- 仅清空历史特定分区的数据并保留表内其他分区
ALTER TABLE sales_records
TRUNCATE PARTITION p2025;底层机制
在 InnoDB 存储引擎中，TRUNCATE TABLE 采用重建表空间的方式运作，完全绕过了逐行扫描与行锁机制。
执行流程
隐式提交活跃事务：Server 层在执行 DDL 前会自动提交当前会话中未提交的事务。
获取元数据排他锁（MDL X-Lock）：阻塞该表的所有并发读写操作，防止表结构被并发修改。
物理表空间重建：
- 在独立表空间模式（
innodb_file_per_table=ON）下，InnoDB 直接在文件系统层面释放旧的.ibd磁盘文件，并重新初始化一个仅含初始 B+ 树根节点的空白.ibd文件。 - 在共享表空间模式下，直接释放该表分配的 Extent（区）并归还给空闲链表。
- 在独立表空间模式（
释放锁与记录日志：重置内存元数据缓存，释放 MDL 锁，并记录 DDL 级别的 Binlog。
核心特性
- 不可回滚（Non-Transactional）：执行后立即生效并触发隐式提交，即使在
START TRANSACTION事务块中也无法执行ROLLBACK撤销。 - 自增计数器重置：表内的自增主键（
AUTO_INCREMENT）计数器会被强制重置为起始默认值（通常为 1）。 - 不激活 DML 触发器：由于没有逐行删除的过程，
TRUNCATE绝不会触发任何BEFORE DELETE或AFTER DELETE触发器。 - 极低的资源开销：不生成逐行的 Undo Log 与 Redo Log，不受大事务 Undo 表空间膨胀或锁超时的影响。
外键限制
当表存在外键关联关系时，MySQL 会对 TRUNCATE 施加严格的安全限制。
父表拒绝截断：当目标表被其他子表通过外键引用时，即使子表中当前没有任何数据，执行
TRUNCATE也会直接报错（Error 1701:Cannot truncate a table referenced in a foreign key constraint）。临时绕过方案：若确认数据可以安全清除，可在当前会话中临时关闭外键检查后再执行：
sql-- 临时禁用外键检查后执行清空并恢复 SET FOREIGN_KEY_CHECKS = 0; TRUNCATE TABLE parent_categories; -- 恢复外键检查以保障后续数据完整性 SET FOREIGN_KEY_CHECKS = 1;
机制对比
| 维度 | TRUNCATE (DDL) | DELETE (DML) | DROP (DDL) |
|---|---|---|---|
| 操作目标 | 清空全部数据并重建表空间 | 逐行扫描并标记删除数据 | 彻底销毁表结构、元数据及物理文件 |
| 过滤条件 | 不支持 WHERE | 支持 WHERE 条件精准删除 | 不支持过滤条件 |
| 事务支持 | 隐式提交，不可回滚 | 完整事务支持，可回滚 | 隐式提交，不可回滚 |
| 磁盘空间释放 | 立即物理释放 .ibd 磁盘空间 | 仅页内标记，不减小 .ibd 文件 | 立即释放所有磁盘空间 |
| 日志记录 | 仅记录 DDL 语句日志 | 逐行记录 Undo Log 与 Redo Log | 仅记录 DDL 语句日志 |
| 自增主键 | 重置为初始值（通常为 1） | 保持当前最大计数值不变 | 计数器与表一并销毁 |
生产规范
- 防范 MDL 锁等待：
TRUNCATE需要获取 MDL 排他锁。如果当前表上有未结束的慢查询或长事务，TRUNCATE将进入锁等待，并随之阻塞该表后续的所有业务读写请求。 - 规避大文件瞬时 I/O 冲击：对几十 GB 以上的超大表执行
TRUNCATE会在操作系统层面瞬间释放巨量数据块，可能引发磁盘 I/O 瞬时毛刺。建议在业务低峰期执行。 - 权限收敛控制：在 MySQL 权限体系中，执行
TRUNCATE必须具备DROP权限。生产环境中不应对普通业务账号授予DROP权限，防止误截断核心表。